#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
基础分类导入脚本
将女性服装分类数据导入到数据库中
"""

import sqlite3
import json
import os
import sys
from datetime import datetime

def get_db_path():
    """获取数据库路径"""
    current_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    db_path = os.path.join(current_dir, "..", "flux_prompt_helper", "promptManager-server", "instance", "prompt_manager.db")
    return db_path

def create_categories_table(cursor):
    """创建分类表"""
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category_key TEXT NOT NULL UNIQUE,
            category_name TEXT NOT NULL,
            description TEXT DEFAULT '',
            parent_id INTEGER DEFAULT NULL,
            level INTEGER DEFAULT 0,
            data TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (parent_id) REFERENCES categories (id)
        )
    """)
    
    # 创建索引
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_categories_key ON categories (category_key)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_categories_parent ON categories (parent_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_categories_level ON categories (level)")

def process_category(cursor, category, parent_id=None, level=0):
    """处理单个分类"""
    category_key = category.get('id', '')
    category_name = category.get('name', '')
    description = category.get('description', '')
    
    # 准备数据
    data = {
        'name': category_name,
        'description': description,
        'examples': category.get('examples', []),
        'attributes': category.get('attributes', []),
        'subcategories': []
    }
    
    # 插入分类
    cursor.execute("""
        INSERT OR REPLACE INTO categories 
        (category_key, category_name, description, parent_id, level, data, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
    """, (category_key, category_name, description, parent_id, level, json.dumps(data, ensure_ascii=False)))
    
    # 获取插入的分类ID
    cursor.execute("SELECT id FROM categories WHERE category_key = ?", (category_key,))
    category_id = cursor.fetchone()[0]
    
    # 处理子分类
    subcategories = category.get('subcategories', [])
    for subcategory in subcategories:
        process_category(cursor, subcategory, category_id, level + 1)
    
    return category_id

def import_categories_from_json():
    """从JSON文件导入分类数据"""
    # 读取JSON文件
    current_dir = os.path.dirname(os.path.abspath(__file__))
    json_path = os.path.join(current_dir, "..", "basic_clothes.json")
    
    if not os.path.exists(json_path):
        print(f"❌ JSON文件不存在: {json_path}")
        return False
    
    with open(json_path, 'r', encoding='utf-8') as f:
        data = json.load(f)
    
    # 获取数据库路径
    db_path = get_db_path()
    if not os.path.exists(os.path.dirname(db_path)):
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
    
    # 连接数据库
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # 创建表
        create_categories_table(cursor)
        
        # 清空现有数据
        cursor.execute("DELETE FROM categories")
        print("🗑️ 清空现有分类数据")
        
        # 处理分类数据
        female_clothing = data.get('femaleClothingClassification', {})
        categories = female_clothing.get('categories', [])
        
        print(f"📂 开始导入 {len(categories)} 个主分类")
        
        for category in categories:
            process_category(cursor, category)
            print(f"✅ 导入分类: {category.get('name', '')}")
        
        # 提交事务
        conn.commit()
        
        # 统计导入结果
        cursor.execute("SELECT COUNT(*) FROM categories")
        total_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM categories WHERE level = 0")
        main_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM categories WHERE level > 0")
        sub_count = cursor.fetchone()[0]
        
        print(f"🎉 导入完成!")
        print(f"📊 总计: {total_count} 个分类")
        print(f"📊 主分类: {main_count} 个")
        print(f"📊 子分类: {sub_count} 个")
        
        return True
        
    except Exception as e:
        print(f"❌ 导入失败: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()

def create_enhanced_categories():
    """创建增强的分类数据"""
    enhanced_categories = {
        "clothing": {
            "name": "服装",
            "description": "各种服装类型和款式",
            "subcategories": {
                "tops": {
                    "name": "上装",
                    "description": "上衣类服装",
                    "items": [
                        {"name": "衬衫", "description": "shirt", "prompt_cn": "衬衫", "prompt_en": "shirt"},
                        {"name": "修身衬衫", "description": "fitted shirt", "prompt_cn": "修身衬衫", "prompt_en": "fitted shirt"},
                        {"name": "透视衬衫", "description": "see-through shirt", "prompt_cn": "透视衬衫", "prompt_en": "see-through shirt"},
                        {"name": "低领衬衫", "description": "low-cut shirt", "prompt_cn": "低领衬衫", "prompt_en": "low-cut shirt"},
                        {"name": "露肩衬衫", "description": "off-shoulder shirt", "prompt_cn": "露肩衬衫", "prompt_en": "off-shoulder shirt"},
                        {"name": "牛仔衬衫", "description": "denim shirt", "prompt_cn": "牛仔衬衫", "prompt_en": "denim shirt"},
                        {"name": "真丝衬衫", "description": "silk shirt", "prompt_cn": "真丝衬衫", "prompt_en": "silk shirt"},
                        {"name": "蕾丝衬衫", "description": "lace shirt", "prompt_cn": "蕾丝衬衫", "prompt_en": "lace shirt"},
                        {"name": "吊带", "description": "tank top", "prompt_cn": "吊带背心", "prompt_en": "tank top"},
                        {"name": "露脐背心", "description": "crop top", "prompt_cn": "露脐背心", "prompt_en": "crop top"},
                        {"name": "蕾丝背心", "description": "lace camisole", "prompt_cn": "蕾丝背心", "prompt_en": "lace camisole"},
                        {"name": "紧身背心", "description": "fitted tank top", "prompt_cn": "紧身背心", "prompt_en": "fitted tank top"},
                        {"name": "背心", "description": "vest", "prompt_cn": "背心", "prompt_en": "vest"},
                        {"name": "T恤", "description": "t-shirt", "prompt_cn": "T恤", "prompt_en": "t-shirt"},
                        {"name": "印花T恤", "description": "printed t-shirt", "prompt_cn": "印花T恤", "prompt_en": "printed t-shirt"},
                        {"name": "紧身T恤", "description": "fitted t-shirt", "prompt_cn": "紧身T恤", "prompt_en": "fitted t-shirt"},
                        {"name": "毛衣", "description": "sweater", "prompt_cn": "毛衣", "prompt_en": "sweater"},
                        {"name": "紧身毛衣", "description": "fitted sweater", "prompt_cn": "紧身毛衣", "prompt_en": "fitted sweater"},
                        {"name": "露肩毛衣", "description": "off-shoulder sweater", "prompt_cn": "露肩毛衣", "prompt_en": "off-shoulder sweater"},
                        {"name": "V领毛衣", "description": "v-neck sweater", "prompt_cn": "V领毛衣", "prompt_en": "v-neck sweater"},
                        {"name": "短款毛衣", "description": "cropped sweater", "prompt_cn": "短款毛衣", "prompt_en": "cropped sweater"},
                        {"name": "高领毛衣", "description": "turtle neck sweater", "prompt_cn": "高领毛衣", "prompt_en": "turtle neck sweater"},
                        {"name": "开衫毛衣", "description": "cardigan", "prompt_cn": "开衫毛衣", "prompt_en": "cardigan"},
                        {"name": "卫衣", "description": "hoodie", "prompt_cn": "卫衣", "prompt_en": "hoodie"}
                    ]
                },
                "dresses": {
                    "name": "裙装",
                    "description": "连衣裙和半身裙",
                    "items": [
                        {"name": "连衣裙", "description": "dress", "prompt_cn": "连衣裙", "prompt_en": "dress"},
                        {"name": "紧身连衣裙", "description": "fitted dress", "prompt_cn": "紧身连衣裙", "prompt_en": "fitted dress"},
                        {"name": "露背连衣裙", "description": "backless dress", "prompt_cn": "露背连衣裙", "prompt_en": "backless dress"},
                        {"name": "高开叉连衣裙", "description": "high-slit dress", "prompt_cn": "高开叉连衣裙", "prompt_en": "high-slit dress"},
                        {"name": "吊带连衣裙", "description": "strapless dress", "prompt_cn": "吊带连衣裙", "prompt_en": "strapless dress"},
                        {"name": "蕾丝连衣裙", "description": "lace dress", "prompt_cn": "蕾丝连衣裙", "prompt_en": "lace dress"},
                        {"name": "短款连衣裙", "description": "short dress", "prompt_cn": "短款连衣裙", "prompt_en": "short dress"},
                        {"name": "长款连衣裙", "description": "long dress", "prompt_cn": "长款连衣裙", "prompt_en": "long dress"},
                        {"name": "半身裙", "description": "skirt", "prompt_cn": "半身裙", "prompt_en": "skirt"},
                        {"name": "迷你裙", "description": "mini skirt", "prompt_cn": "迷你裙", "prompt_en": "mini skirt"},
                        {"name": "高腰裙", "description": "high-waisted skirt", "prompt_cn": "高腰裙", "prompt_en": "high-waisted skirt"},
                        {"name": "包臀裙", "description": "pencil skirt", "prompt_cn": "包臀裙", "prompt_en": "pencil skirt"},
                        {"name": "开叉裙", "description": "slit skirt", "prompt_cn": "开叉裙", "prompt_en": "slit skirt"},
                        {"name": "短裙", "description": "short skirt", "prompt_cn": "短裙", "prompt_en": "short skirt"},
                        {"name": "长裙", "description": "long skirt", "prompt_cn": "长裙", "prompt_en": "long skirt"},
                        {"name": "牛仔裙", "description": "denim skirt", "prompt_cn": "牛仔裙", "prompt_en": "denim skirt"},
                        {"name": "皮裙", "description": "leather skirt", "prompt_cn": "皮裙", "prompt_en": "leather skirt"},
                        {"name": "格子裙", "description": "plaid skirt", "prompt_cn": "格子裙", "prompt_en": "plaid skirt"},
                        {"name": "百褶裙", "description": "pleated skirt", "prompt_cn": "百褶裙", "prompt_en": "pleated skirt"},
                        {"name": "A字裙", "description": "A-line skirt", "prompt_cn": "A字裙", "prompt_en": "A-line skirt"},
                        {"name": "一步裙", "description": "pencil skirt", "prompt_cn": "一步裙", "prompt_en": "pencil skirt"}
                    ]
                },
                "bottoms": {
                    "name": "下装",
                    "description": "下装类服装",
                    "items": [
                        {"name": "牛仔裤", "description": "jeans", "prompt_cn": "牛仔裤", "prompt_en": "jeans"},
                        {"name": "紧身牛仔裤", "description": "skinny jeans", "prompt_cn": "紧身牛仔裤", "prompt_en": "skinny jeans"},
                        {"name": "破洞牛仔裤", "description": "ripped jeans", "prompt_cn": "破洞牛仔裤", "prompt_en": "ripped jeans"},
                        {"name": "直筒牛仔裤", "description": "straight jeans", "prompt_cn": "直筒牛仔裤", "prompt_en": "straight jeans"},
                        {"name": "阔腿牛仔裤", "description": "wide-leg jeans", "prompt_cn": "阔腿牛仔裤", "prompt_en": "wide-leg jeans"},
                        {"name": "短裤", "description": "shorts", "prompt_cn": "短裤", "prompt_en": "shorts"},
                        {"name": "热裤", "description": "hot pants", "prompt_cn": "热裤", "prompt_en": "hot pants"},
                        {"name": "高腰短裤", "description": "high-waisted shorts", "prompt_cn": "高腰短裤", "prompt_en": "high-waisted shorts"},
                        {"name": "紧身短裤", "description": "tight shorts", "prompt_cn": "紧身短裤", "prompt_en": "tight shorts"},
                        {"name": "蕾丝短裤", "description": "lace shorts", "prompt_cn": "蕾丝短裤", "prompt_en": "lace shorts"},
                        {"name": "牛仔短裤", "description": "denim shorts", "prompt_cn": "牛仔短裤", "prompt_en": "denim shorts"},
                        {"name": "皮短裤", "description": "leather shorts", "prompt_cn": "皮短裤", "prompt_en": "leather shorts"},
                        {"name": "长裤", "description": "pants", "prompt_cn": "长裤", "prompt_en": "pants"},
                        {"name": "紧身裤", "description": "leggings", "prompt_cn": "紧身裤", "prompt_en": "leggings"},
                        {"name": "皮裤", "description": "leather pants", "prompt_cn": "皮裤", "prompt_en": "leather pants"},
                        {"name": "高腰裤", "description": "high-waisted pants", "prompt_cn": "高腰裤", "prompt_en": "high-waisted pants"},
                        {"name": "阔腿裤", "description": "wide-leg pants", "prompt_cn": "阔腿裤", "prompt_en": "wide-leg pants"},
                        {"name": "运动裤", "description": "sweatpants", "prompt_cn": "运动裤", "prompt_en": "sweatpants"},
                        {"name": "休闲裤", "description": "casual pants", "prompt_cn": "休闲裤", "prompt_en": "casual pants"},
                        {"name": "西裤", "description": "dress pants", "prompt_cn": "西裤", "prompt_en": "dress pants"}
                    ]
                },
                "outerwear": {
                    "name": "外套",
                    "description": "外套类服装",
                    "items": [
                        {"name": "夹克", "description": "jacket", "prompt_cn": "夹克", "prompt_en": "jacket"},
                        {"name": "皮夹克", "description": "leather jacket", "prompt_cn": "皮夹克", "prompt_en": "leather jacket"},
                        {"name": "牛仔夹克", "description": "denim jacket", "prompt_cn": "牛仔夹克", "prompt_en": "denim jacket"},
                        {"name": "风衣", "description": "trench coat", "prompt_cn": "风衣", "prompt_en": "trench coat"},
                        {"name": "羽绒服", "description": "down jacket", "prompt_cn": "羽绒服", "prompt_en": "down jacket"},
                        {"name": "西装", "description": "suit", "prompt_cn": "西装", "prompt_en": "suit"},
                        {"name": "大衣", "description": "coat", "prompt_cn": "大衣", "prompt_en": "coat"},
                        {"name": "呢大衣", "description": "wool coat", "prompt_cn": "呢大衣", "prompt_en": "wool coat"},
                        {"name": "小香风外套", "description": "chanel-style jacket", "prompt_cn": "小香风外套", "prompt_en": "chanel-style jacket"}
                    ]
                }
            }
        },
        "underwear": {
            "name": "内衣",
            "description": "内衣类服装",
            "subcategories": {
                "bras": {
                    "name": "文胸",
                    "description": "文胸类内衣",
                    "items": [
                        {"name": "文胸", "description": "bra", "prompt_cn": "文胸", "prompt_en": "bra"},
                        {"name": "全罩杯文胸", "description": "full cup bra", "prompt_cn": "全罩杯文胸", "prompt_en": "full cup bra"},
                        {"name": "3/4罩杯文胸", "description": "3/4 cup bra", "prompt_cn": "3/4罩杯文胸", "prompt_en": "3/4 cup bra"},
                        {"name": "1/2罩杯文胸", "description": "half cup bra", "prompt_cn": "1/2罩杯文胸", "prompt_en": "half cup bra"},
                        {"name": "无钢圈文胸", "description": "wireless bra", "prompt_cn": "无钢圈文胸", "prompt_en": "wireless bra"},
                        {"name": "聚拢型文胸", "description": "push-up bra", "prompt_cn": "聚拢型文胸", "prompt_en": "push-up bra"},
                        {"name": "无肩带文胸", "description": "strapless bra", "prompt_cn": "无肩带文胸", "prompt_en": "strapless bra"},
                        {"name": "蕾丝文胸", "description": "lace bra", "prompt_cn": "蕾丝文胸", "prompt_en": "lace bra"},
                        {"name": "运动文胸", "description": "sports bra", "prompt_cn": "运动文胸", "prompt_en": "sports bra"},
                        {"name": "光面文胸", "description": "smooth bra", "prompt_cn": "光面文胸", "prompt_en": "smooth bra"}
                    ]
                },
                "panties": {
                    "name": "内裤",
                    "description": "内裤类内衣",
                    "items": [
                        {"name": "内裤", "description": "panties", "prompt_cn": "内裤", "prompt_en": "panties"},
                        {"name": "三角裤", "description": "briefs", "prompt_cn": "三角裤", "prompt_en": "briefs"},
                        {"name": "丁字裤", "description": "thong", "prompt_cn": "丁字裤", "prompt_en": "thong"},
                        {"name": "高腰内裤", "description": "high-waisted panties", "prompt_cn": "高腰内裤", "prompt_en": "high-waisted panties"},
                        {"name": "无痕内裤", "description": "seamless panties", "prompt_cn": "无痕内裤", "prompt_en": "seamless panties"},
                        {"name": "蕾丝内裤", "description": "lace panties", "prompt_cn": "蕾丝内裤", "prompt_en": "lace panties"},
                        {"name": "情趣内裤", "description": "sexy panties", "prompt_cn": "情趣内裤", "prompt_en": "sexy panties"},
                        {"name": "平角裤", "description": "boyshorts", "prompt_cn": "平角裤", "prompt_en": "boyshorts"},
                        {"name": "C字裤", "description": "C-string", "prompt_cn": "C字裤", "prompt_en": "C-string"}
                    ]
                },
                "sleepwear": {
                    "name": "睡衣",
                    "description": "睡衣类内衣",
                    "items": [
                        {"name": "吊带睡裙", "description": "slip dress", "prompt_cn": "吊带睡裙", "prompt_en": "slip dress"},
                        {"name": "丝绸吊带裙", "description": "silk slip dress", "prompt_cn": "丝绸吊带裙", "prompt_en": "silk slip dress"},
                        {"name": "蕾丝吊带裙", "description": "lace slip dress", "prompt_cn": "蕾丝吊带裙", "prompt_en": "lace slip dress"},
                        {"name": "透明吊带裙", "description": "transparent slip dress", "prompt_cn": "透明吊带裙", "prompt_en": "transparent slip dress"},
                        {"name": "情趣睡裙", "description": "sexy nightdress", "prompt_cn": "情趣睡裙", "prompt_en": "sexy nightdress"},
                        {"name": "睡袍", "description": "nightgown", "prompt_cn": "睡袍", "prompt_en": "nightgown"},
                        {"name": "两件套睡衣", "description": "pajama set", "prompt_cn": "两件套睡衣", "prompt_en": "pajama set"},
                        {"name": "真丝睡衣", "description": "silk pajamas", "prompt_cn": "真丝睡衣", "prompt_en": "silk pajamas"},
                        {"name": "蕾丝睡衣", "description": "lace pajamas", "prompt_cn": "蕾丝睡衣", "prompt_en": "lace pajamas"}
                    ]
                },
                "shapewear": {
                    "name": "塑身衣",
                    "description": "塑身类内衣",
                    "items": [
                        {"name": "塑身连体衣", "description": "shapewear bodysuit", "prompt_cn": "塑身连体衣", "prompt_en": "shapewear bodysuit"},
                        {"name": "塑身背心", "description": "shapewear vest", "prompt_cn": "塑身背心", "prompt_en": "shapewear vest"},
                        {"name": "塑身裤", "description": "shapewear pants", "prompt_cn": "塑身裤", "prompt_en": "shapewear pants"},
                        {"name": "塑身腰封", "description": "waist cincher", "prompt_cn": "塑身腰封", "prompt_en": "waist cincher"},
                        {"name": "魔术塑身衣", "description": "magic shapewear", "prompt_cn": "魔术塑身衣", "prompt_en": "magic shapewear"}
                    ]
                }
            }
        },
        "shoes": {
            "name": "鞋类",
            "description": "各种鞋类",
            "subcategories": {
                "high_heels": {
                    "name": "高跟鞋",
                    "description": "高跟鞋类",
                    "items": [
                        {"name": "高跟鞋", "description": "high heels", "prompt_cn": "高跟鞋", "prompt_en": "high heels"},
                        {"name": "细跟高跟鞋", "description": "stiletto heels", "prompt_cn": "细跟高跟鞋", "prompt_en": "stiletto heels"},
                        {"name": "粗跟高跟鞋", "description": "chunky heels", "prompt_cn": "粗跟高跟鞋", "prompt_en": "chunky heels"},
                        {"name": "防水台高跟鞋", "description": "platform heels", "prompt_cn": "防水台高跟鞋", "prompt_en": "platform heels"},
                        {"name": "尖头高跟鞋", "description": "pointed heels", "prompt_cn": "尖头高跟鞋", "prompt_en": "pointed heels"},
                        {"name": "圆头高跟鞋", "description": "round toe heels", "prompt_cn": "圆头高跟鞋", "prompt_en": "round toe heels"}
                    ]
                },
                "flat_shoes": {
                    "name": "平底鞋",
                    "description": "平底鞋类",
                    "items": [
                        {"name": "芭蕾平底鞋", "description": "ballet flats", "prompt_cn": "芭蕾平底鞋", "prompt_en": "ballet flats"},
                        {"name": "乐福鞋", "description": "loafers", "prompt_cn": "乐福鞋", "prompt_en": "loafers"},
                        {"name": "穆勒鞋", "description": "mules", "prompt_cn": "穆勒鞋", "prompt_en": "mules"},
                        {"name": "单鞋", "description": "pumps", "prompt_cn": "单鞋", "prompt_en": "pumps"}
                    ]
                },
                "boots": {
                    "name": "靴子",
                    "description": "靴子类",
                    "items": [
                        {"name": "短靴", "description": "ankle boots", "prompt_cn": "短靴", "prompt_en": "ankle boots"},
                        {"name": "长靴", "description": "knee-high boots", "prompt_cn": "长靴", "prompt_en": "knee-high boots"},
                        {"name": "过膝靴", "description": "over-the-knee boots", "prompt_cn": "过膝靴", "prompt_en": "over-the-knee boots"},
                        {"name": "马丁靴", "description": "combat boots", "prompt_cn": "马丁靴", "prompt_en": "combat boots"},
                        {"name": "雪地靴", "description": "snow boots", "prompt_cn": "雪地靴", "prompt_en": "snow boots"}
                    ]
                },
                "sandals": {
                    "name": "凉鞋",
                    "description": "凉鞋类",
                    "items": [
                        {"name": "凉鞋", "description": "sandals", "prompt_cn": "凉鞋", "prompt_en": "sandals"},
                        {"name": "夹趾凉鞋", "description": "flip-flops", "prompt_cn": "夹趾凉鞋", "prompt_en": "flip-flops"},
                        {"name": "一字带凉鞋", "description": "strappy sandals", "prompt_cn": "一字带凉鞋", "prompt_en": "strappy sandals"},
                        {"name": "罗马凉鞋", "description": "gladiator sandals", "prompt_cn": "罗马凉鞋", "prompt_en": "gladiator sandals"},
                        {"name": "坡跟凉鞋", "description": "wedge sandals", "prompt_cn": "坡跟凉鞋", "prompt_en": "wedge sandals"}
                    ]
                }
            }
        },
        "accessories": {
            "name": "配饰",
            "description": "各种配饰",
            "subcategories": {
                "bags": {
                    "name": "包包",
                    "description": "包包类配饰",
                    "items": [
                        {"name": "手提包", "description": "handbag", "prompt_cn": "手提包", "prompt_en": "handbag"},
                        {"name": "单肩包", "description": "shoulder bag", "prompt_cn": "单肩包", "prompt_en": "shoulder bag"},
                        {"name": "双肩包", "description": "backpack", "prompt_cn": "双肩包", "prompt_en": "backpack"},
                        {"name": "斜挎包", "description": "crossbody bag", "prompt_cn": "斜挎包", "prompt_en": "crossbody bag"},
                        {"name": "钱包", "description": "wallet", "prompt_cn": "钱包", "prompt_en": "wallet"},
                        {"name": "手拿包", "description": "clutch", "prompt_cn": "手拿包", "prompt_en": "clutch"}
                    ]
                },
                "jewelry": {
                    "name": "首饰",
                    "description": "首饰类配饰",
                    "items": [
                        {"name": "项链", "description": "necklace", "prompt_cn": "项链", "prompt_en": "necklace"},
                        {"name": "耳环", "description": "earrings", "prompt_cn": "耳环", "prompt_en": "earrings"},
                        {"name": "手镯", "description": "bracelet", "prompt_cn": "手镯", "prompt_en": "bracelet"},
                        {"name": "戒指", "description": "ring", "prompt_cn": "戒指", "prompt_en": "ring"},
                        {"name": "胸针", "description": "brooch", "prompt_cn": "胸针", "prompt_en": "brooch"}
                    ]
                },
                "scarves": {
                    "name": "围巾",
                    "description": "围巾类配饰",
                    "items": [
                        {"name": "真丝围巾", "description": "silk scarf", "prompt_cn": "真丝围巾", "prompt_en": "silk scarf"},
                        {"name": "羊毛围巾", "description": "wool scarf", "prompt_cn": "羊毛围巾", "prompt_en": "wool scarf"},
                        {"name": "棉质围巾", "description": "cotton scarf", "prompt_cn": "棉质围巾", "prompt_en": "cotton scarf"},
                        {"name": "印花围巾", "description": "printed scarf", "prompt_cn": "印花围巾", "prompt_en": "printed scarf"}
                    ]
                },
                "hats": {
                    "name": "帽子",
                    "description": "帽子类配饰",
                    "items": [
                        {"name": "贝雷帽", "description": "beret", "prompt_cn": "贝雷帽", "prompt_en": "beret"},
                        {"name": "棒球帽", "description": "baseball cap", "prompt_cn": "棒球帽", "prompt_en": "baseball cap"},
                        {"name": "渔夫帽", "description": "bucket hat", "prompt_cn": "渔夫帽", "prompt_en": "bucket hat"},
                        {"name": "礼帽", "description": "fedora", "prompt_cn": "礼帽", "prompt_en": "fedora"}
                    ]
                }
            }
        }
    }
    
    return enhanced_categories

def import_enhanced_categories():
    """导入增强的分类数据"""
    db_path = get_db_path()
    if not os.path.exists(os.path.dirname(db_path)):
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # 创建表
        create_categories_table(cursor)
        
        # 清空现有数据
        cursor.execute("DELETE FROM categories")
        print("🗑️ 清空现有分类数据")
        
        # 获取增强分类数据
        enhanced_categories = create_enhanced_categories()
        
        print(f"📂 开始导入增强分类数据")
        
        for category_key, category_data in enhanced_categories.items():
            # 插入主分类
            cursor.execute("""
                INSERT INTO categories 
                (category_key, category_name, description, parent_id, level, data, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
            """, (
                category_key,
                category_data['name'],
                category_data['description'],
                None,
                0,
                json.dumps(category_data, ensure_ascii=False)
            ))
            
            # 获取主分类ID
            cursor.execute("SELECT id FROM categories WHERE category_key = ?", (category_key,))
            main_category_id = cursor.fetchone()[0]
            
            print(f"✅ 导入主分类: {category_data['name']}")
            
            # 处理子分类
            subcategories = category_data.get('subcategories', {})
            for sub_key, sub_data in subcategories.items():
                cursor.execute("""
                    INSERT INTO categories 
                    (category_key, category_name, description, parent_id, level, data, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
                """, (
                    f"{category_key}_{sub_key}",
                    sub_data['name'],
                    sub_data['description'],
                    main_category_id,
                    1,
                    json.dumps(sub_data, ensure_ascii=False)
                ))
                
                print(f"  ✅ 导入子分类: {sub_data['name']}")
        
        # 提交事务
        conn.commit()
        
        # 统计导入结果
        cursor.execute("SELECT COUNT(*) FROM categories")
        total_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM categories WHERE level = 0")
        main_count = cursor.fetchone()[0]
        
        cursor.execute("SELECT COUNT(*) FROM categories WHERE level > 0")
        sub_count = cursor.fetchone()[0]
        
        print(f"🎉 导入完成!")
        print(f"📊 总计: {total_count} 个分类")
        print(f"📊 主分类: {main_count} 个")
        print(f"📊 子分类: {sub_count} 个")
        
        return True
        
    except Exception as e:
        print(f"❌ 导入失败: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()

def main():
    """主函数"""
    print("🚀 开始导入基础分类数据")
    print("=" * 50)
    
    # 选择导入方式
    print("请选择导入方式:")
    print("1. 从JSON文件导入 (basic_clothes.json)")
    print("2. 导入增强分类数据 (推荐)")
    
    choice = input("请输入选择 (1 或 2): ").strip()
    
    if choice == "1":
        success = import_categories_from_json()
    elif choice == "2":
        success = import_enhanced_categories()
    else:
        print("❌ 无效选择")
        return
    
    if success:
        print("✅ 分类数据导入成功!")
    else:
        print("❌ 分类数据导入失败!")

if __name__ == "__main__":
    main()
